﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using _619BJP.Models;

namespace _619BJP.Database.Product
{
    public class productDB
    {
        private string connString = System.Configuration.ConfigurationManager.ConnectionStrings["619BJP.Data.ConnectionString"].ConnectionString;

        /// <summary>
        /// 产品详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ProductModel productDetail(int id)
        {
            ProductModel product = new ProductModel();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                addViewCount(id);

                string sql = "SELECT pp.id,pp.product_name,pp.brand_id,b.name as brand_name,pp.img_url,pp.view_count,pp.description," +
                            "pinfo.category_id,pcate.category_name,pinfo.producer,pinfo.approve_number,pinfo.product_specification,pinfo.health_functions,"+
                            "pinfo.suit_crowds,pinfo.market_price "+
                            "FROM [619bjp].[619bjp].[PR_Product] as pp "+
                            "left join PR_ProductInfo as pinfo on  pp.id = pinfo.product_id "+
                            "left join PR_ProductCategory as pcate on pinfo.category_id = pcate.category_id " +
                            "left join BR_Brand as b on pp.brand_id = b.id where pp.id = " + id;
                SqlDataReader sdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sql);
                if (sdr.Read())
                {
                    product.ProductID = Convert.ToInt32(sdr["id"].ToString());
                    product.product_name = sdr["product_name"].ToString();
                    product.brand_id = int.Parse(sdr["brand_id"].ToString());
                    product.brand_name = sdr["brand_name"].ToString();
                    product.img_url = sdr["img_url"].ToString();
                    product.view_count = int.Parse(sdr["view_count"].ToString());
                    product.description = sdr["description"].ToString();
                    product.category_id = int.Parse(sdr["category_id"].ToString());
                    product.category_name = sdr["category_name"].ToString();
                    product.producer = sdr["producer"].ToString();
                    product.approve_number = sdr["approve_number"].ToString();
                    product.product_specification = sdr["product_specification"].ToString();
                    product.health_functions = sdr["health_functions"].ToString();
                    product.suit_crowds = sdr["suit_crowds"].ToString();
                    product.market_price =  float.Parse(sdr["market_price"].ToString());
                }
            }
            return product;
        }

        /// <summary>
        /// 某品牌下的产品列表
        /// </summary>
        /// <returns></returns>
        public DataSet GetProductByBrand(int brand_id,int count)
        {
            DataSet ds = new DataSet();
            string sql = "";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                if (count > 0)
                {
                    sql = "select top " + count + " p.id,p.product_name,p.description,p.img_url,p.view_count,pinfo.market_price " +
                            "from PR_Product as p left join PR_ProductInfo as pinfo on p.id = pinfo.product_id where p.brand_id = " + brand_id + " order by p.id";
                }
                else
                {
                    sql = "select p.id,p.product_name,p.description,p.img_url,p.view_count,pinfo.market_price " +
                            "from PR_Product as p left join PR_ProductInfo as pinfo on p.id = pinfo.product_id where p.brand_id = " + brand_id + " order by p.id";
                }
                ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
            }
            return ds;
        }

        /// <summary>
        /// 某类别下的产品列表
        /// </summary>
        /// <returns></returns>
        public DataSet GetProductsByCategory(int cate_id,int count)
        {
            DataSet ds = new DataSet();
            string sql = "";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                if (count > 0)
                {
                    sql = "select top " + count + " p.id,p.product_name,p.description,p.img_url,p.view_count,pinfo.market_price,pinfo.category_id " +
                            "from PR_Product as p left join PR_ProductInfo as pinfo on p.id = pinfo.product_id where pinfo.category_id = " + cate_id + " order by p.id";
                }
                else
                {
                    sql = "select p.id,p.product_name,p.description,p.img_url,p.view_count,pinfo.market_price,pinfo.category_id " +
                            "from PR_Product as p left join PR_ProductInfo as pinfo on p.id = pinfo.product_id where pinfo.category_id = " + cate_id + " order by p.id";
                }
                ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
            }
            return ds;
        }

        /// <summary>
        /// 产品类别信息
        /// </summary>
        /// <param name="cate_id"></param>
        /// <returns></returns>
        public ProductCategoryModel GetProductsCategory(int cate_id)
        {
            ProductCategoryModel category = new ProductCategoryModel();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                string sql = "SELECT * FROM PR_ProductCategory where category_id = " + cate_id;
                SqlDataReader sdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sql);
                if (sdr.Read())
                {
                    category.category_id = int.Parse(sdr["category_id"].ToString());
                    category.category_name = sdr["category_name"].ToString();
                }
            }
            return category;
        }

        /// <summary>
        /// 产品对比
        /// </summary>
        /// <param name="product_id"></param>
        /// <param name="cate_id"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public DataSet GetProductsForCompare(int product_id, int cate_id, int count)
        {
            DataSet ds = new DataSet();
            string sql = "";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                sql = "select top " + count + " p.id,p.product_name,p.description,p.img_url,p.view_count,pinfo.market_price,pinfo.category_id " +
                        "from PR_Product as p left join PR_ProductInfo as pinfo on p.id = pinfo.product_id " +
                        "where pinfo.category_id = " + cate_id + " and p.id !=" + product_id + " order by p.id";
                ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
            }
            return ds;
        }

        /// <summary>
        /// 产品对比详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataSet GetProductsForCompareDetail(string ids)
        {
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                if (!string.IsNullOrEmpty(ids))
                {
                    string sql = "SELECT pp.id,pp.product_name,pp.brand_id,b.name as brand_name,pp.img_url," +
                                "pinfo.category_id,pcate.category_name,pinfo.producer,pinfo.approve_number,pinfo.product_specification,pinfo.health_functions," +
                                "pinfo.suit_crowds,pinfo.market_price " +
                                "FROM [619bjp].[619bjp].[PR_Product] as pp " +
                                "left join PR_ProductInfo as pinfo on  pp.id = pinfo.product_id " +
                                "left join PR_ProductCategory as pcate on pinfo.category_id = pcate.category_id " +
                                "left join BR_Brand as b on pp.brand_id = b.id where pp.id in (" + ids + ")";
                    ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
                }
            }
            return ds;
        }

        /// <summary>
        /// 添加产品查看次数
        /// </summary>
        /// <param name="id"></param>
        public void addViewCount(int id)
        {
            int count = 0;
            string sql = "";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                sql = "SELECT view_count FROM [PR_Product] where id = " + id;
                count = Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.Text, sql));

                int newcount = count + 1;
                sql = "update [PR_Product] set view_count = " + newcount + " where id = " + id;
                Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.Text, sql));
            }
        }

        /// <summary>
        /// 最受关注（按浏览数排序）
        /// </summary>
        /// <param name="count"></param>
        /// <returns></returns>
        public DataSet GetProductListOrderByView(int count)
        {
            DataSet ds = new DataSet();
            string sql = "";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                sql = "SELECT top " + count + " pp.id,pp.product_name,pp.img_url,pinfo.suit_crowds " +
                                "FROM [619bjp].[619bjp].[PR_Product] as pp " +
                                "left join PR_ProductInfo as pinfo on  pp.id = pinfo.product_id " +
                                " order by view_count desc";
                ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
            }
            return ds;
        }

        /// <summary>
        ///  产品类别
        /// </summary>
        /// <param name="count"></param>
        /// <returns></returns>
        public DataSet GetProductCategory()
        {
            DataSet ds = new DataSet();
            string sql = "";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                sql = "select category_id,category_name from PR_ProductCategory order by category_id";
                ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
            }
            return ds;
        }

        /// <summary>
        ///  产品类别，根据所拥有的产品数倒序排列
        /// </summary>
        /// <param name="count"></param>
        /// <returns></returns>
        public DataSet GetCategoryOrderbyProductCount(int count)
        {
            DataSet ds = new DataSet();
            string sql = "";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                sql = "select top " + count + " pc.category_id,pc.category_name,pc_count.c from PR_ProductCategory as pc " +
                    "right join ( SELECT category_id,count(id) as c from PR_ProductInfo group by category_id) as pc_count " +
                    "on pc.category_id = pc_count.category_id order by pc_count.c desc";
                ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
            }
            return ds;
        }
    }
}
